C#读取excel文件，并生成json

2024-06-03 14:42| 来源: 网络整理| 查看: 265

这次介绍两种方法，第一种是安装AccessDatabaseEngine，第二种是利用Npoi读取excel

一、第一种利用AccessDatabaseEngine进行读取excel文件

1.安装AccessDatabaseEngine

链接地址:http://download.microsoft.com/download/7/0/3/703ffbcb-dc0c-4e19-b0da-1463960fdcdb/AccessDatabaseEngine.exe

2.根据Excel文件获取所有的Sheet名称，获取每一个sheet的内容组装dataTable

（1）根据Excel文件获取所有的sheet名称

public List GetExcelSheetNames(string filePath) { OleDbConnection connection = null; System.Data.DataTable dt = null; try { String connectionString = String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 12.0;HDR=YES;IMEX=2;'", filePath); connection = new OleDbConnection(connectionString); connection.Open(); dt = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); if (dt == null) { return new List(); } String[] excelSheets = new String[dt.Rows.Count]; int i = 0; foreach (DataRow row in dt.Rows) { excelSheets[i] = row["TABLE_NAME"].ToString().Split('$')[0]; i++; } return excelSheets.Distinct().ToList(); } catch (Exception ex) { return new List(); } finally { if (connection != null) { connection.Close(); connection.Dispose(); } if (dt != null) { dt.Dispose(); } } }

（2）获取每一个Sheet的内容组装dataTable

public DataTable GetExcelContent(String filePath, string sheetName) { if (sheetName == "_xlnm#_FilterDatabase") return null; DataSet dateSet = new DataSet(); String connectionString = String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 12.0;HDR=NO;IMEX=2;'", filePath); String commandString = string.Format("SELECT * FROM [{0}$]", sheetName); using (OleDbConnection connection = new OleDbConnection(connectionString)) { connection.Open(); using (OleDbCommand command = new OleDbCommand(commandString, connection)) { OleDbCommand objCmd = new OleDbCommand(commandString, connection); OleDbDataAdapter myData = new OleDbDataAdapter(commandString, connection); myData.Fill(dateSet, sheetName); DataTable table = dateSet.Tables[sheetName]; for (int i = 0; i < table.Rows[0].ItemArray.Length; i++) { var cloumnName = table.Rows[0].ItemArray[i].ToString(); if (!string.IsNullOrEmpty(cloumnName)) table.Columns[i].ColumnName = cloumnName; } table.Rows.RemoveAt(0); return table; } } }

（3）table转json

public object ExcelToJson(string filePath) { string localPath = Server.MapPath(filePath); List tableNames = GetExcelSheetNames(localPath); var json = new JObject(); tableNames.ForEach(tableName => { var table = new JArray() as dynamic; DataTable dataTable = GetExcelContent(localPath, tableName); foreach (DataRow dataRow in dataTable.Rows) { dynamic row = new JObject(); foreach (DataColumn column in dataTable.Columns) { row.Add(column.ColumnName, dataRow[column.ColumnName].ToString()); } table.Add(row); } json.Add(tableName, table); }); return json.ToString(); }

最终生成的字符串:

二、利用NPOI读取excel

1.将excel文件中的内容读取出来，存放到DataSet中

#region 将Excel中的内容转换成DataSet /// /// 将Excel中的内容转换成DataSet /// /// 路径 /// 第一行的文本 /// public static DataSet ImportExcelToDataSet(string filePath,List excelHead) { DataSet ds = new DataSet(); IWorkbook workbook; string fileExt = Path.GetExtension(filePath).ToLower(); try { using (FileStream fs = new FileStream(filePath, FileMode.Open, FileAccess.Read)) { if (fileExt == ".xlsx") { workbook = new XSSFWorkbook(fs);//2007之后版本的excel } else { workbook = new HSSFWorkbook(fs);//2003版本的excel } for (int a = 0, b = workbook.NumberOfSheets; a < b; a++) { //获取读取的Sheet表的索引 ISheet sheet = workbook.GetSheetAt(a); DataTable table = new DataTable(); IRow headerRow = sheet.GetRow(sheet.FirstRowNum); int cellCount = headerRow.LastCellNum; //将第一行的文本作为列名 for (int i = headerRow.FirstCellNum; i < cellCount; i++) { DataColumn column; object obj = GetValueType(headerRow.GetCell(i)); if (obj == null || obj.ToString() == string.Empty) { column = new DataColumn("Columns" + i.ToString()); } 　　　　　　　　　　　　　　　　　else{ column = new DataColumn(GetType(obj.ToString())); } table.Columns.Add(column); } //读取第一行下面的数据,将他们作为数据行存储 for (int i = (sheet.FirstRowNum + 1); i

【本文地址】

公司简介

联系我们

今日新闻

推荐新闻

专题文章